﻿CREATE TRIGGER [dbo].[tr_Payment] 
   ON  dbo.t_Payment 
INSTEAD OF INSERT
AS 
IF @@ROWCOUNT = 0 RETURN

SET NOCOUNT ON

IF TRIGGER_NESTLEVEL(OBJECT_ID('vr_Payment#ForInsert')) > 0 RETURN

PRINT OBJECT_NAME(@@PROCID)

DECLARE @Deleted TABLE(LoanID int,Amount numeric(18,2),DatePaid datetime,WFID int)
DECLARE @LoanID int,@Amount numeric(18,2),@DatePaid datetime,@WFID int
DECLARE curInserted CURSOR LOCAL FOR
   SELECT LoanID, Amount, DatePaid,WaterFallID
   FROM   inserted ORDER BY ID

OPEN curInserted
FETCH NEXT FROM curInserted INTO @LoanID,@Amount,@DatePaid,@WFID
WHILE @@fetch_status = 0 BEGIN

	DELETE t_Payment
	OUTPUT deleted.LoanID,deleted.Amount,deleted.DatePaid,deleted.WaterFallID INTO @Deleted
	FROM t_Payment WHERE LoanID = @LoanID AND DatePaid > @DatePaid

	SELECT 'X',@LoanID,@Amount,@DatePaid,@WFID

	INSERT INTO v_Payment#ForInsert(LoanID, Amount, DatePaid,WaterFallID)
	SELECT @LoanID,@Amount,@DatePaid,@WFID
	
	IF EXISTS(SELECT * FROM @Deleted)
		INSERT INTO t_Payment(LoanID,Amount,DatePaid)
		SELECT LoanID,Amount,DatePaid FROM @Deleted
		
	DELETE FROM @Deleted

	IF @@ERROR <> 0 BEGIN
		ROLLBACK TRAN
		BREAK
	END

	FETCH NEXT FROM curInserted INTO @LoanID,@Amount,@DatePaid,@WFID
BREAK
END

CLOSE curInserted
DEALLOCATE curInserted

RETURN --===================================================================

GO
DISABLE TRIGGER [dbo].[tr_Payment]
    ON [dbo].[t_Payment];

